package com.tsu.mall.common.mapper;

import com.tsu.mall.common.VO.BillWaresVO;
import com.tsu.mall.common.VO.IndexTableBingVO;
import com.tsu.mall.common.VO.WaresClassifyVO;
import com.tsu.mall.common.entity.Attachment;
import com.tsu.mall.common.entity.Wares;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.tsu.mall.common.entity.Waresclassify;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author tttttt
 * @since 2023-03-17
 */
public interface WaresMapper extends BaseMapper<Wares> {

    List<Wares> doSelectListPage(String keyword,String waresStatus);


    @Select("select * from waresclassify where parent_id=#{id}")
    List<Waresclassify> selectChildren(Integer id);


    @Select("select parent_id from waresclassify where id in (select waresclassify_id from wares where id=#{id})")
    Integer selectWaresParent_id(Integer id);

    @Select("select * from wares where wares_name=#{waresName}")
    List<Wares> check(String waresName);

    void deleteWares(Integer[] ids);

    void upShelfWares(Integer[] ids);

    void downShelfWares(Integer[] ids);


    @Update("update attachment set source_id=#{id} where id=#{ids}")
    void uploadAttachmentByIds(@Param("ids") Integer ids,@Param("id") Integer id);

    @Select("select * from attachment where source=#{source} and source_id=#{pid}")
    List<Attachment> doSelectList(@Param("pid") Integer pid, @Param("source") String source);

    List<Attachment> selectByids(String[] ids);

    List<WaresClassifyVO> selectClassifyList();

    @Select("select * from wares where waresclassify_id=#{id} and status='已上架'")
    List<Wares> selectBySecondId(Integer id);


@Select("SELECT\n" +
        "  b.id,b.classify_name\n" +
        "FROM\n" +
        "    waresclassify a\n" +
        "    INNER JOIN waresclassify b ON a.id = b.parent_id\n" +
        "    LEFT JOIN waresclassify c ON a.id = c.parent_id AND b.sequence < c.sequence\n" +
        "WHERE\n" +
        "    a.parent_id = 0 AND c.id IS NULL\n" +
        "ORDER BY\n" +
        "    a.id ASC, b.sequence DESC;")
    List<Waresclassify> doSelectClassifyList();

    @Select("select *\n" +
            "from wares\n" +
            "where waresclassify_id=#{id}\n" +
            "ORDER BY price\n" +
            "limit 0,1")
    Wares cheap(Integer id);

    @Select("SELECT bill_detail.status,bill_detail.amount,bill_detail.member_wares_color,bill_detail.member_wares_version,bill_detail.member_wares_size,wares.wares_name,\n" +
            "wares.price,wares.breaks,wares.pre_price,wares.main_pic,wares.feature\n" +
            "FROM\n" +
            "bill_detail\n" +
            "inner join wares on bill_detail.wares_id=wares.id\n" +
            "where bill_detail.bill_id=#{billId}")
    List<BillWaresVO> selectWaresByBillId(Integer billId);

    @Select("SELECT *\n" +
            "from wares\n" +
            "where id in(\n" +
            "SELECT wares_id\n" +
            "from bill_detail\n" +
            "where `status`='已到货'\n" +
            "GROUP BY wares_id\n" +
            "ORDER BY COUNT(wares_id) DESC\n" +
            ")\n" +
            "LIMIT 0,5")
    List<Wares> selectSellTop5Wares();

    @Select("SELECT *\n" +
            "from wares\n" +
            "where id in(\n" +
            "SELECT wares_id\n" +
            "from bill_detail\n" +
            "where `status`='已到货'\n" +
            "GROUP BY wares_id\n" +
            "ORDER BY COUNT(wares_id) DESC\n" +
            ")\n" +
            "LIMIT 5,10")
    List<Wares> selectSellTop6to10WaresInfo();

    @Select("select wares.wares_name as name,COUNT(bill_detail.wares_id) as value\n" +
            "from wares\n" +
            "inner join bill_detail on wares.id=bill_detail.wares_id\n" +
            "GROUP BY bill_detail.wares_id\n" +
            "order by COUNT(bill_detail.wares_id) DESC\n" +
            "limit 0,5")
    List<IndexTableBingVO> dateBing();
}
